﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[fnBkp_GetBackupList_RecoveryWindow]
#-- Purpose:		Returns a list of backups for a database that are needed
#--					to restore within the defined recovery window.
#--	Last Update:	02/23/2015
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE FUNCTION [dbo].[fnBkp_GetBackupList_RecoveryWindow]
(	
	@database_name			sysname
)
RETURNS @returntable TABLE 
(
	id							bigint,
	recovery_path_broken		bit,
	backup_set_id				int,
	db_backup_set_id			int,
	prev_tlog_backup_set_id		int,
	database_name				nvarchar(128),
	name						nvarchar(128),
	type						char(1),
	backup_start_date			datetime,
	has_bulk_logged_data		bit,
	backup_size					numeric(20,0),
	physical_device_name		nvarchar(260),
	file_exists					bit,
	database_backup_lsn			numeric(25,0),
	differential_base_lsn		numeric(25,0),
	checkpoint_lsn				numeric(25,0),
	fork_point_lsn				numeric(25,0),
	first_lsn					numeric(25,0),
	last_lsn					numeric(25,0),
	first_recovery_fork_guid	uniqueidentifier,
	last_recovery_fork_guid		uniqueidentifier
)
AS
BEGIN	
	DECLARE @rec_window_min_id int, @min_diff_full int, @recovery_window int

	INSERT INTO @returntable
		SELECT		id,
					recovery_path_broken,
					backup_set_id,
					db_backup_set_id,
					prev_tlog_backup_set_id,
					database_name,
					name,
					type,
					backup_start_date,
					has_bulk_logged_data,
					backup_size,
					physical_device_name,
					file_exists,
					database_backup_lsn,
					differential_base_lsn,
					checkpoint_lsn,
					fork_point_lsn,
					first_lsn,
					last_lsn,
					first_recovery_fork_guid,
					last_recovery_fork_guid
		FROM		[dbo].[fnBkp_GetBackupList](@database_name, 1, NULL, NULL) t1
		ORDER BY	id

	--- Determine the configured Recovery Windows Days
	SELECT		@recovery_window = ISNULL(cfg_recover_window_days, 7)
	FROM		[dbo].[tblMaint_Config]

	--- Default to 8 days if nothing is found in tblConfiguration
	IF @recovery_window IS NULL
		SET @recovery_window = 7

	--- What is the minimum id within the recovery window?
	SELECT		@rec_window_min_id = MIN(id)
	FROM		@returntable
	WHERE		backup_start_date > DATEADD(dd, (-1 * @recovery_window), GETDATE())

	--- What is the minimum full or differential backup prior to that?
	SELECT		@min_diff_full = MAX(id)
	FROM		@returntable
	WHERE		id < @rec_window_min_id
				AND type IN ('D', 'I')

	--- What is the first tlog backup after the minimum full or differential?
	SELECT		@rec_window_min_id = MIN(t2.id)
	FROM		@returntable t1
	JOIN		@returntable t2 ON t1.backup_set_id <= t2.db_backup_set_id
				AND t2.type = 'L'
	WHERE		t1.id = @min_diff_full
		
	--- Delete records from prior to these minimum values
	DELETE
	FROM		@returntable
	WHERE		id NOT IN (
					SELECT		id
					FROM		@returntable
					WHERE		(
									id IN (1, @min_diff_full)
									OR id >= @rec_window_min_id
									OR (
										type IN ('D', 'I')
										AND backup_start_date > DATEADD(dd, (-1 * @recovery_window), GETDATE())
									)
								)
				)

	RETURN
END